package com.ztesoft.zsmart.zcm.dialing.util;

import com.google.common.base.Strings;
import com.ztesoft.zsmart.zcm.dialing.domain.TransferTaskInstance;
import com.ztesoft.zsmart.zcm.dialing.domain.TransferTaskTimer;
import org.springframework.util.CollectionUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author xuduan
 * @Date 2020/12/29 0029 22:10
 */
public class JDBCUtil {

    private static final String DRIVER_MYSQL = "com.mysql.jdbc.Driver";

    /**
     * 得到nms数据库连接
     *
     * @param url
     * @param password
     * @param userName
     * @return
     * */
    public static Connection getNmsJdbcConnection(String url, String userName, String password) throws ClassNotFoundException, SQLException {
        if (Strings.isNullOrEmpty(url) || Strings.isNullOrEmpty(userName) || Strings.isNullOrEmpty(password)) {
            return null;
        }
        Class.forName(DRIVER_MYSQL);
        DriverManager.setLoginTimeout(60);
        return DriverManager.getConnection("jdbc:mysql://" + url + "/zcm_nms?allowMultiQueries=true&rewriteBatchedStatements=true", userName, password);
    }

    /**
     * 得到cmdb数据库连接
     *
     * @param url
     * @param password
     * @param userName
     * @return
     * */
    public static Connection getCmdbJdbcConnection(String url, String userName, String password) throws ClassNotFoundException, SQLException {
        if (Strings.isNullOrEmpty(url) || Strings.isNullOrEmpty(userName) || Strings.isNullOrEmpty(password)) {
            return null;
        }
        Class.forName(DRIVER_MYSQL);
        DriverManager.setLoginTimeout(60);
        return DriverManager.getConnection("jdbc:mysql://" + url + "/zcm_cmdb?allowMultiQueries=true&rewriteBatchedStatements=true", userName, password);
    }

    /**
     * 查询可转移任务
     *
     * @param ids
     * @param noIds
     * @param name
     * @param start
     * @param pageSize
     * @param typeList
     * @param statement
     * */
    public static List<TransferTaskInstance> listALLTransferTask(Statement statement, List<Integer> ids, List<Integer> noIds, List<String> typeList, String name, Integer start, Integer pageSize) throws SQLException {
        ResultSet resultSet = null;
        List<TransferTaskInstance> list = new ArrayList<>();
        try {
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append("SELECT i.task_id as taskId, i.input_value as value FROM nm_comp_template t LEFT OUTER JOIN nm_comp_instance i ON t.comp_tmp_id = i.comp_tmp_id AND i.state = 'a' ")
                    .append("LEFT JOIN nm_task_lib b ON i.task_id = b.task_id WHERE t.state = 'a' ");
            if (!CollectionUtils.isEmpty(ids)) {
                stringBuffer.append("and i.task_id in (");
                for (int i = 0; i < ids.size() - 1; i++) {
                    stringBuffer.append(ids.get(i)).append(",");
                }
                stringBuffer.append(ids.get(ids.size() - 1)).append(") ");
            }
            if (!CollectionUtils.isEmpty(noIds)) {
                stringBuffer.append("and i.task_id not in (");
                for (int i = 0; i < noIds.size() - 1; i++) {
                    stringBuffer.append(noIds.get(i)).append(",");
                }
                stringBuffer.append(noIds.get(noIds.size() - 1)).append(") ");
            }
            if (!CollectionUtils.isEmpty(typeList)) {
                stringBuffer.append("and i.input_value in (");
                for (int i = 0; i < typeList.size() - 1; i++) {
                    stringBuffer.append("\"").append(typeList.get(i)).append("\",");
                }
                stringBuffer.append("\"").append(typeList.get(typeList.size() - 1)).append("\") ");
            }
            if (!Strings.isNullOrEmpty(name)) {
                stringBuffer.append("AND b.task_name LIKE \"%").append(name).append("%\" ");
            }
            stringBuffer.append("order by i.task_id ASC ");
            if (null != start && null != pageSize) {
                stringBuffer.append("limit ").append(start).append(",").append(pageSize).append(";");
            }
            resultSet = statement.executeQuery(stringBuffer.toString());
            while (resultSet.next()) {
                TransferTaskInstance instance = new TransferTaskInstance();
                instance.setTaskId(Integer.valueOf(resultSet.getString("taskId")));
                instance.setValue(resultSet.getString("value"));
                list.add(instance);
            }
        }
        finally {
            if (null != resultSet) {
                resultSet.close();
            }
        }
        return list;
    }

    /**
     * 查询可转移任务总数量
     *
     * @param name
     * @param typeList
     * @param statement
     * @return
     * */
    public static Integer countALLTransferTask(Statement statement, List<String> typeList, String name) throws SQLException {
        ResultSet resultSet = null;
        Integer count = null;
        try {
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append("SELECT count(*) AS COUNT FROM nm_comp_template t LEFT OUTER JOIN nm_comp_instance i ON t.comp_tmp_id = i.comp_tmp_id AND i.state = 'a' ")
                    .append("LEFT JOIN nm_task_lib b ON i.task_id = b.task_id WHERE t.state = 'a' and i.input_value in (");
            if (!CollectionUtils.isEmpty(typeList)) {
                for (int i = 0; i < typeList.size() - 1; i++) {
                    stringBuffer.append("\"").append(typeList.get(i)).append("\",");
                }
                stringBuffer.append("\"").append(typeList.get(typeList.size() - 1)).append("\") ");
            }
            if (!Strings.isNullOrEmpty(name)) {
                stringBuffer.append("AND b.task_name LIKE \"%").append(name).append("%\" ");
            }
            resultSet = statement.executeQuery(stringBuffer.toString());
            while (resultSet.next()) {
                count = Integer.valueOf(resultSet.getString("COUNT"));
            }
            return count;
        }
        finally {
            if (null != resultSet) {
                resultSet.close();
            }
        }
    }

    /**
     * 根据taskIds查询任务的详细信息
     *
     * @param statement
     * @param taskIds
     * @return
     * */
    public static List<TransferTaskInstance> listTransferTaskInstanceByIds(Statement statement, List<Integer> taskIds) throws SQLException {
        ResultSet resultSet = null;
        List<TransferTaskInstance> list = new ArrayList<>();
        try {
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append("SELECT a.task_id AS taskId, t.name_val AS nameVal, a.input_value AS value, b.task_name AS taskName, b.server_id AS serverId, b.run_plan_id AS runPlanId FROM ")
                    .append("nm_comp_template t LEFT JOIN nm_comp_instance a ON t.comp_tmp_id = a.comp_tmp_id AND t.state = 'a' = a.state = 'a' ")
                    .append("LEFT JOIN nm_task_lib b ON a.task_id = b.task_id WHERE a.task_id IN (");
            if (!CollectionUtils.isEmpty(taskIds)) {
                for (int i = 0; i < taskIds.size() - 1; i++) {
                    stringBuffer.append(taskIds.get(i)).append(",");
                }
                stringBuffer.append(taskIds.get(taskIds.size() - 1)).append(") ");
            }
            stringBuffer.append("ORDER BY a.task_id ASC");
            resultSet = statement.executeQuery(stringBuffer.toString());
            while (resultSet.next()) {
                TransferTaskInstance instance = new TransferTaskInstance();
                String taskIdString = resultSet.getString("taskId");
                String serverIdString = resultSet.getString("serverId");
                String runPlanId = resultSet.getString("runPlanId");
                instance.setTaskId(Strings.isNullOrEmpty(taskIdString) ? null : Integer.valueOf(taskIdString));
                instance.setValue(resultSet.getString("value"));
                instance.setNameVal(resultSet.getString("nameVal"));
                instance.setTaskName(resultSet.getString("taskName"));
                instance.setServerId(Strings.isNullOrEmpty(serverIdString) ? null : Integer.valueOf(serverIdString));
                instance.setRunPlanId(Strings.isNullOrEmpty(runPlanId) ? null : Integer.valueOf(runPlanId));
                list.add(instance);
            }
        }
        finally {
            if (null != resultSet) {
                resultSet.close();
            }
        }
        return list;
    }

    /**
     * 根据ids查询执行计划信息
     *
     * @param statement
     * @param ids
     * @return
     * */
    public static List<TransferTaskTimer> listTransferTaskTimer(Statement statement, List<Integer> ids) throws SQLException {
        ResultSet resultSet = null;
        List<TransferTaskTimer> list = new ArrayList<>();
        try {
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append("SELECT run_plan_id as id, cron_expression as cronExpression, time_unit as timeUnit, cycle_unit as cycleUnit ")
                    .append("FROM  nm_run_plan WHERE run_plan_id in (");
            if (!CollectionUtils.isEmpty(ids)) {
                for (int i = 0; i < ids.size() - 1; i++) {
                    stringBuffer.append(ids.get(i)).append(",");
                }
                stringBuffer.append(ids.get(ids.size() - 1)).append(") ");
            }
            resultSet = statement.executeQuery(stringBuffer.toString());
            while (resultSet.next()) {
                TransferTaskTimer timer = new TransferTaskTimer();
                String id = resultSet.getString("id");
                String cycleUnit = resultSet.getString("cycleUnit");
                timer.setId(Strings.isNullOrEmpty(id) ? null : Integer.valueOf(id));
                timer.setCronExpression(resultSet.getString("cronExpression"));
                timer.setCycleUnit(Strings.isNullOrEmpty(cycleUnit) ?  null : BigDecimal.valueOf(Double.valueOf(cycleUnit)));
                list.add(timer);
            }
        }
        finally {
            if (null != resultSet) {
                resultSet.close();
            }
        }
        return list;
    }

    public String getName() {
        return this.getClass().toString();
    }
}
